﻿using System;
using System.Collections.Generic;
using System.Text;
using CNVP.Framework.Helper;
using System.Data;
using System.Data.SqlClient;
using CNVP.Config;
using CNVP.Framework.Utils;

namespace CNVP.JShop.Publish
{
    public class CommonData
    {
        private static String Prefix = DBConfig.Prefix;
        #region "路径导航"
        /// <summary>
        /// 路径导航
        /// </summary>
        /// <param name="LoopStr"></param>
        /// <param name="ParentID"></param>
        /// <param name="RootShow"></param>
        /// <returns></returns>
        public static String GetColumnPath(String LoopStr, Int32 ParentID, String RootShow)
        {
            String _Str = String.Empty;
            switch (RootShow)
            {
                //显示全部的栏目
                case "1":
                    _Str = GetAllColumnName(LoopStr, ParentID, true);
                    break;
                //顶级栏目不显示
                case "2":
                    _Str = GetAllColumnName(LoopStr, ParentID, false);
                    break;
                //仅显示当前栏目
                case "3":
                    _Str = GetLastColumnName(LoopStr, ParentID);
                    break;
            }
            return _Str;
        }
        /// <summary>
        /// 显示全部的栏目
        /// </summary>
        /// <param name="LoopStr"></param>
        /// <param name="ColumnID"></param>
        /// <returns></returns>
        private static String GetAllColumnName(String LoopStr,Int32 ColumnID,bool ShowRoot)
        {
            String _ReturnStr = String.Empty;
            DataTable Dt = GetColumnPathDt(ColumnID);
            if (Dt != null && Dt.Rows.Count > 0)
            {
                String _Str = LoopStr;
                _Str = _Str.Replace("{#ColumnName}", Dt.Rows[0]["ColumnName"].ToString());
                String _ISBlank = String.Empty;
                //if (Dt.Rows[0]["ISBlank"].ToString() == "1")
                //{
                //    _ISBlank = "' target='_blank'";
                //}
                String _ColumnUrl = Dt.Rows[0]["ColumnUrl"].ToString();
                if (!String.IsNullOrEmpty(_ColumnUrl))
                {
                    _Str = _Str.Replace("{#ColumnUrl}", String.Format("{0}{1}", _ColumnUrl, _ISBlank));
                }
                else
                {
                    if (Dt.Rows[0]["ColumnType"].ToString() == "3")
                    {
                        _Str = _Str.Replace("{#ColumnUrl}", String.Format("{0}ProductList_{1}.aspx", UIConfig.InstallDir, Dt.Rows[0]["ID"].ToString()));
                    }
                    else if (Dt.Rows[0]["ColumnType"].ToString() == "2")
                    {
                        _Str = _Str.Replace("{#ColumnUrl}", String.Format("{0}NewsList_{1}.aspx", UIConfig.InstallDir, Dt.Rows[0]["ID"].ToString()));
                    }
                }
                if (Dt.Rows[0]["Depth"].ToString() != "0")
                {
                    _Str = GetAllColumnName(LoopStr, Convert.ToInt32(Dt.Rows[0]["Depth"]), ShowRoot) + _Str;
                }
                if (ShowRoot || Dt.Rows[0]["Depth"].ToString() != "0")
                {
                    _ReturnStr = _Str;
                }
            }
            return _ReturnStr;
        }
        /// <summary>
        /// 仅显示当前栏目
        /// </summary>
        /// <param name="LoopStr"></param>
        /// <param name="ColumnID"></param>
        /// <returns></returns>
        private static String GetLastColumnName(String LoopStr,Int32 ColumnID)
        {
            String _Str = LoopStr;
            DataTable Dt = GetColumnPathDt(ColumnID);
            if (Dt != null && Dt.Rows.Count > 0)
            {
                _Str = _Str.Replace("{#ColumnName}", Dt.Rows[0]["ColumnName"].ToString());
                String _ISBlank = String.Empty;

                String _ColumnUrl=Dt.Rows[0]["ColumnUrl"].ToString();
                if (!String.IsNullOrEmpty(_ColumnUrl))
                {
                    _Str = _Str.Replace("{#ColumnUrl}", String.Format("{0}{1}", _ColumnUrl, _ISBlank));
                }
                else
                {
                    if (Dt.Rows[0]["ColumnType"].ToString() == "3")
                    {
                        _Str = _Str.Replace("{#ColumnUrl}", String.Format("{0}ProductList_{1}.aspx", UIConfig.InstallDir, Dt.Rows[0]["ColumnID"].ToString()));
                    }
                    else if (Dt.Rows[0]["ColumnType"].ToString() == "2")
                    {
                        _Str = _Str.Replace("{#ColumnUrl}", String.Format("{0}NewsList_{1}.aspx", UIConfig.InstallDir, Dt.Rows[0]["ColumnID"].ToString()));
                    }
                }
            }
            return _Str;
        }
        /// <summary>
        /// 返回数据源
        /// </summary>
        /// <param name="ColumnID"></param>
        /// <returns></returns>
        private static DataTable GetColumnPathDt(Int32 ColumnID)
        {
            String sql = String.Empty;
            DataTable Dt = new DataTable();
            if (ColumnID == 0)
            {
                sql = "Select Top 1 * From " + Prefix + "Column where IsShow=1 Order By OrderID Desc";
                Dt = DbHelper.ExecuteTable(sql, null);
            }
            else
            {
                sql = "Select * From " + Prefix + "Column  where IsShow=1 and  ID=@ColumnID";
                SqlParameter[] param = { DbHelper.MakeInParam("@ColumnID", SqlDbType.Int, 4, ColumnID) };
                Dt = DbHelper.ExecuteTable(sql, param);
            }
            return Dt;
        }
        #endregion 

        #region "获取指定SQL语句的数据"
        /// <summary>
        /// 获取指定SQL语句的数据
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns></returns>
        public static DataTable GetCommonContent(String strSql)
        {
            return DbHelper.ExecuteTable(strSql);
        }
        #endregion

        #region "无限级显示栏目导航"
        #region "读取全部可显示与导航的栏目"
        /// <summary>
        /// 读取全部可显示与导航的栏目
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetAllColumn(String columnType)
        {
            String sql = "Select * From [" + Prefix + "Column] Where [IsShow]=1 ";
            SqlParameter[] param = null;
            if (!String.IsNullOrEmpty(columnType) && Public.IsNumber(columnType))
            {
                sql += " and [ColumnType]=@ColumnType ";
                param = new SqlParameter[] { DbHelper.MakeInParam("@ColumnType", SqlDbType.Int, 4, columnType) };
            }
            sql += " Order By [OrderID],[ID]";
            return DbHelper.ExecuteTable(sql,param);
        }
        #endregion
        #region "栏目导航"
        /// <summary>
        /// 栏目导航
        /// </summary>
        /// <returns></returns>
        public static DataRow[] GetColumn(DataTable dt,String ShowRoot, Int32 ColumnID, String ShowCol)
        {
            DataRow[] dr = null;

            switch (ShowRoot)
            {
                //仅显示第一级栏目
                case "1":
                    dr =GetTopColumn(dt,ShowCol);
                    break;
                //当前栏目的父级栏目
                case "2":
                    dr = GetParentColumn(dt,ShowCol,ColumnID);
                    break;
                //当前栏目的子栏目
                case "3":
                    dr = GetChildColumn(dt, ShowCol, ColumnID);
                    break;
                //当前栏目同级栏目
                case "4":
                    dr = GetSameColumn(dt, ShowCol, ColumnID);
                    break;
                //指定栏目的子栏目
                case "5":
                    dr = GetChildColumn(dt, ShowCol, ColumnID);
                    break;
                //指定栏目同级栏目
                case "6":
                    dr = GetSameColumn(dt, ShowCol, ColumnID);
                    break;
                default:
                    dr=null;
                    break;
            }
            return dr;

        }
        #endregion
        #region "读取顶级栏目"
        /// <summary>
        /// 读取顶级栏目
        /// </summary>
        /// <param name="dt">全部栏目</param>
        /// <param name="ShowCol">显示行数</param>
        /// <returns>DataRow[]</returns>
        private static DataRow[] GetTopColumn(DataTable dt, String ShowCol)
        {
            DataRow[] dr=null;
            dr = dt.Select("[Depth]='0'");
            if (!String.IsNullOrEmpty(ShowCol) && Public.IsNumber(ShowCol) && dr.Length>0)
            {
                Int32 c=(dr.Length>=Convert.ToInt32(ShowCol)?Convert.ToInt32(ShowCol):dr.Length);
                DataRow[] dtr= new DataRow[c];
                for (Int32 i = 0; i < c; i++)
                {
                    dtr[i] = dr[i];
                }
                return dtr;
            }
            else
            {
                return dr;
            }
        }
        #endregion
        #region "读取父级栏目"
        /// <summary>
        /// 读取父级栏目
        /// </summary>
        /// <param name="dt">全部栏目</param>
        /// <param name="ShowCol">显示行数</param>
        /// <param name="ColumnID">栏目ID</param>
        /// <returns>DataRow[]</returns>
        private static DataRow[] GetParentColumn(DataTable dt, String ShowCol, Int32 ColumnID)
        {
            DataRow[] dr = null;
            DataRow[] temp = dt.Select("[ID]='" + ColumnID.ToString()+"'");
            if (temp != null && temp.Length > 0)
            {
                String path = (temp[0]["AddressRoute"] == null || Convert.ToString(temp[0]["AddressRoute"]) == "0") ? "0" : Convert.ToString(temp[0]["AddressRoute"]).Remove(Convert.ToString(temp[0]["AddressRoute"]).LastIndexOf(','));
                
                dr = dt.Select("[AddressRoute]='" + path + "'");
                if (!String.IsNullOrEmpty(ShowCol) && Public.IsNumber(ShowCol) && dr.Length > 0)
                {
                    Int32 c = (dr.Length >= Convert.ToInt32(ShowCol) ? Convert.ToInt32(ShowCol) : dr.Length);
                    DataRow[] dtr = new DataRow[c];
                    for (Int32 i = 0; i < c; i++)
                    {
                        dtr[i] = dr[i];
                    }
                    return dtr;
                }
                else
                {
                    return dr;
                }
            }
            else
            {
                return dr;
            }
        }
        #endregion
        #region "读取子级栏目"
        /// <summary>
        /// 读取子级栏目
        /// </summary>
        /// <param name="dt">全部栏目</param>
        /// <param name="ShowCol">显示行数</param>
        /// <param name="ColumnID">栏目ID</param>
        /// <returns>DataRow[]</returns>
        private static DataRow[] GetChildColumn(DataTable dt, String ShowCol, Int32 ColumnID)
        {
            DataRow[] dr = null;
            dr = dt.Select("[Depth]='" + ColumnID.ToString()+"'" );
            if (!String.IsNullOrEmpty(ShowCol) && Public.IsNumber(ShowCol) && dr.Length > 0)
            {
                Int32 c = (dr.Length >= Convert.ToInt32(ShowCol) ? Convert.ToInt32(ShowCol) : dr.Length);
                DataRow[] dtr = new DataRow[c];
                for (Int32 i = 0; i < c; i++)
                {
                    dtr[i] = dr[i];
                }
                return dtr;
            }
            else
            {
                return dr;
            }
        }
        #endregion
        #region "读取同级栏目"
        /// <summary>
        /// 读取同级栏目
        /// </summary>
        /// <param name="dt">全部栏目</param>
        /// <param name="ShowCol">显示行数</param>
        /// <param name="columnType">栏目类型</param>
        /// <param name="ColumnID">栏目ID</param>
        /// <returns>DataRow[]</returns>
        private static DataRow[] GetSameColumn(DataTable dt, String ShowCol, Int32 ColumnID)
        {
            DataRow[] dr = null;
            DataRow[] temp = dt.Select("[ID]='" + ColumnID.ToString()+"'");
            if (temp != null && temp.Length > 0)
            {
                String path = (temp[0]["AddressRoute"] == null || Convert.ToString(temp[0]["AddressRoute"]) == "0") ? "0" : Convert.ToString(temp[0]["AddressRoute"]);
                if (path != "0")
                {
                    path = path.Remove(path.LastIndexOf(','));
                }
                
                dr = dt.Select("[AddressRoute]='" + path + "'");
                if (!String.IsNullOrEmpty(ShowCol) && Public.IsNumber(ShowCol) && dr.Length > 0)
                {
                    Int32 c = (dr.Length >= Convert.ToInt32(ShowCol) ? Convert.ToInt32(ShowCol) : dr.Length);
                    DataRow[] dtr = new DataRow[c];
                    for (Int32 i = 0; i < c; i++)
                    {
                        dtr[i] = dr[i];
                    }
                    return dtr;
                }
                else
                {
                    return dr;
                }
            }
            else
            {
                return dr;
            }
        }
        #endregion
        #endregion

        #region "栏目导航"
        /// <summary>
        /// 获取全部栏目
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAllColumnList()
        {
            String sql = "Select * From " + Prefix + "Column Where ColumnUrl='' Order By ID Desc";
            return DbHelper.ExecuteTable(sql, null);
        }
        /// <summary>
        /// 栏目导航
        /// </summary>
        /// <returns></returns>
        public static DataTable GetColumnList(String ShowRoot, Int32 ColumnID, String ShowCol)
        {
            DataTable Dt = new DataTable();
            switch (ShowRoot)
            {
                //仅显示第一级栏目
                case "1":
                    Dt = GetParentColumnList(ShowCol);
                    break;
                //当前栏目的父栏目
                case "2":
                    Dt = GetTop1ColumnList(ColumnID);
                    break;
                //当前栏目的子栏目
                case "3":
                    Dt = GetChildColumnList(ColumnID);
                    break;
                //当前栏目同级栏目
                case "4":
                    Dt = GetSameColumnList(ColumnID);
                    break;
                //指定栏目的子栏目
                case "5":
                    Dt = GetChildColumnList(ColumnID);
                    break;
                //指定栏目同级栏目
                case "6":
                    Dt = GetSameColumnList(ColumnID);
                    break;
            }
            return Dt;
        }
        public static DataTable GetColumnList(String ShowRoot, Int32 ColumnID, String columnType, String ShowCol)
        {
            DataTable Dt = new DataTable();
            switch (ShowRoot)
            {
                //仅显示第一级栏目
                case "1":
                    Dt = GetParentColumnList(columnType,ShowCol);
                    break;
                //当前栏目的父栏目
                case "2":
                    Dt = GetTop1ColumnList(ColumnID, columnType);
                    break;
                //当前栏目的子栏目
                case "3":
                    Dt = GetChildColumnList(ColumnID, columnType);
                    break;
                //当前栏目同级栏目
                case "4":
                    Dt = GetSameColumnList(ColumnID, columnType);
                    break;
                //指定栏目的子栏目
                case "5":
                    Dt = GetChildColumnList(ColumnID, columnType);
                    break;
                //指定栏目同级栏目
                case "6":
                    Dt = GetSameColumnList(ColumnID, columnType);
                    break;
            }
            return Dt;
        }
        /// <summary>
        /// 仅显示第一级栏目
        /// </summary>
        /// <returns></returns>
        private static DataTable GetParentColumnList(String ShowCol)
        {
            String sql = "Select top " + ShowCol + " * From " + Prefix + "Column Where IsShow=1 And Depth=0 Order By OrderID,ID Asc";
            return DbHelper.ExecuteTable(sql, null);
        }
        private static DataTable GetParentColumnList(String columnType, String ShowCol)
        {
            String sql = "Select top " + ShowCol + " * From " + Prefix + "Column Where IsShow=1 And Depth=0 and ColumnType=@ColumnType Order By OrderID,ID Asc";
            SqlParameter[] param = { DbHelper.MakeInParam("@ColumnType", SqlDbType.Int, 4, columnType) };
            return DbHelper.ExecuteTable(sql, param);
        }
        //当前栏目的父栏目
        private static DataTable GetTop1ColumnList(Int32 ColumnID)
        {
            Int32 _ColumnID = GetTop1ParentID(ColumnID);
            String sql = "Select * From " + Prefix + "Column Where IsShow=1 And Depth=@Depth";
            SqlParameter[] param = { DbHelper.MakeInParam("@Depth", SqlDbType.Int, 4, _ColumnID) };
            return DbHelper.ExecuteTable(sql, param);
        }
        private static DataTable GetTop1ColumnList(Int32 ColumnID, String columnType)
        {
            Int32 _ColumnID = GetTop1ParentID(ColumnID);
            String sql = "Select * From " + Prefix + "Column Where IsShow=1 And Depth=@Depth and ColumnType=@ColumnType";
            SqlParameter[] param = { 
                                       DbHelper.MakeInParam("@Depth", SqlDbType.Int, 4, _ColumnID), 
                                       DbHelper.MakeInParam("@ColumnType", SqlDbType.Int, 4, columnType) 
                                   };
            return DbHelper.ExecuteTable(sql, param);
        }
        /// <summary>
        /// 获取第一级栏目序号
        /// </summary>
        /// <param name="ColumnID"></param>
        /// <returns></returns>
        private static Int32 GetTop1ParentID(Int32 ColumnID)
        {
            Int32 _ColumnID = ColumnID;
            String sql = "Select top 1 Depth From " + Prefix + "Column Where ID=@ColumnID And Depth>0";
            SqlParameter[] param = { DbHelper.MakeInParam("@ColumnID", SqlDbType.Int, 4, ColumnID) };
            DataTable Dt = DbHelper.ExecuteTable(sql, param);
            if (Dt != null && Dt.Rows.Count > 0)
            {
                _ColumnID = GetTop1ParentID(Convert.ToInt32(Dt.Rows[0]["Depth"]));
            }
            return _ColumnID;
        }
        private static Int32 GetTop1ParentID(Int32 ColumnID, String columnType)
        {
            Int32 _ColumnID = ColumnID;
            String sql = "Select top 1 Depth From " + Prefix + "Column Where ID=@ColumnID And Depth>0 and ColumnType=@ColumnType";
            SqlParameter[] param = { DbHelper.MakeInParam("@ColumnID", SqlDbType.Int, 4, ColumnID), 
                                       DbHelper.MakeInParam("@ColumnType", SqlDbType.Int, 4, columnType)  };
            DataTable Dt = DbHelper.ExecuteTable(sql, param);
            if (Dt != null && Dt.Rows.Count > 0)
            {
                _ColumnID = GetTop1ParentID(Convert.ToInt32(Dt.Rows[0]["Depth"]));
            }
            return _ColumnID;
        }
        /// <summary>
        /// 当前栏目的子栏目
        /// </summary>
        /// <returns></returns>
        public static DataTable GetChildColumnList(Int32 ColumnID)
        {

            String sql = "Select * From " + Prefix + "Column Where IsShow=1 And Depth=@ColumnID Order By OrderID,ID Asc";
            SqlParameter[] param = { DbHelper.MakeInParam("@ColumnID", SqlDbType.Int, 4, ColumnID) };
            return DbHelper.ExecuteTable(sql, param);
        }
        private static DataTable GetChildColumnList(Int32 ColumnID, String columnType)
        {

            String sql = "Select * From " + Prefix + "Column Where IsShow=1 And Depth=@ColumnID and ColumnType=@ColumnType Order By OrderID,ID Asc";
            SqlParameter[] param = { DbHelper.MakeInParam("@ColumnID", SqlDbType.Int, 4, ColumnID), 
                                       DbHelper.MakeInParam("@ColumnType", SqlDbType.Int, 4, columnType) };
            return DbHelper.ExecuteTable(sql, param);
        }
        /// <summary>
        /// 当前栏目同级栏目
        /// </summary>
        /// <returns></returns>
        private static DataTable GetSameColumnList(Int32 ColumnID)
        {
            Int32 _Depth = 0;
            if (ColumnID != 0)
            {
                _Depth = GetParentID(ColumnID);
            }
            String sql = "Select * From " + Prefix + "Column Where IsShow=1 And Depth=@Depth Order By OrderID,ID Asc";
            SqlParameter[] param = { DbHelper.MakeInParam("@Depth", SqlDbType.Int, 4, _Depth) };
            return DbHelper.ExecuteTable(sql, param);
        }
        /// <summary>
        /// 读取同级栏目
        /// </summary>
        /// <param name="ColumnID">栏目ID</param>
        /// <param name="columnType">栏目类型</param>
        /// <returns>DataTable</returns>
        private static DataTable GetSameColumnList(Int32 ColumnID, String columnType)
        {
            Int32 _Depth = 0;
            if (ColumnID != 0)
            {
                _Depth = GetParentID(ColumnID);
            }
            String sql = "Select * From " + Prefix + "Column Where IsShow=1 And Depth=@Depth and ColumnType=@ColumnType Order By OrderID,ID Asc";
            SqlParameter[] param = { DbHelper.MakeInParam("@Depth", SqlDbType.Int, 4, _Depth), 
                                       DbHelper.MakeInParam("@ColumnType", SqlDbType.Int, 4, columnType) };
            return DbHelper.ExecuteTable(sql, param);
        }
        /// <summary>
        /// 获取当前栏目的父类序号值
        /// </summary>
        /// <param name="ColumnID">栏目ID</param>
        /// <returns>父级ID</returns>
        private static Int32 GetParentID(Int32 ColumnID)
        {
            String sql = "Select top 1 [Depth] From [" + Prefix + "Column] Where [ID]=@ColumnID";
            SqlParameter[] param = { DbHelper.MakeInParam("@ColumnID", SqlDbType.Int, 4, ColumnID) };
            return Convert.ToInt32(DbHelper.ExecuteTable(sql, param).Rows[0]["Depth"]);
        }
        #endregion

        public static DataTable GetPropertyValue(Int32 productID)
        {
            String sql = String.Empty;
            if (productID != 0)
            {
                sql = "select b.ExtendProPertyName ,a.StandardVaule as StandardValue from " + Prefix + "ProExtendeProperty as a left join " + Prefix + "ProTypeRefExtendProperty  as b on a.ProTypeRefExtendPropertyID = b.ID where a.ProductID=@ProductID and  b.IsShow=1 order by b.IsOrder asc,b.ID asc";
                SqlParameter[] param = { DbHelper.MakeInParam("@ProductID", SqlDbType.Int, 4, productID) };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "select b.ExtendProPertyName ,a.StandardVaule as StandardValue from " + Prefix + "ProExtendeProperty as a left join " + Prefix + "ProTypeRefExtendProperty  as b on a.ProTypeRefExtendPropertyID = b.ID where a.ProductID=(select top 1 id from " + Prefix + "Products where IsDelete=0 order by ID asc) and  b.IsShow=1 order by b.IsOrder asc,b.ID asc";
                return DbHelper.ExecuteTable(sql);
            }

        }
        public static DataTable GetDescripttionValue(Int32 productID)
        {
            String sql = String.Empty;
            if (productID != 0)
            {
                sql = "select b.DescripttionName ,a.StandardValue from " + Prefix + "ProPropertyDescripttion as a left join " + Prefix + "ProTypeRefDescripttion  as b on a.ProTypeRefDescripttionID = b.ID where a.ProductID=@ProductID order by b.ID asc";
                SqlParameter[] param = { DbHelper.MakeInParam("@ProductID", SqlDbType.Int, 4, productID) };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "select b.DescripttionName ,a.StandardValue from " + Prefix + "ProPropertyDescripttion as a left join " + Prefix + "ProTypeRefDescripttion  as b on a.ProTypeRefDescripttionID = b.ID where a.ProductID=(select top 1 id from " + Prefix + "Products where IsDelete=0 order by ID asc) order by b.ID asc";
                return DbHelper.ExecuteTable(sql);
            }

        }
        #region "商品列表"
        /// <summary>
        /// 商品列表
        /// </summary>
        /// <param name="ProductClassID"></param>
        /// <returns></returns>
        public static DataTable GetProductList(Int32 ProductClassID)
        {
            String sql = String.Empty;
            if (ProductClassID != 0)
            {
                sql = "Select * From " + Prefix + "Products Where IsDelete=0 and ProductClassID=@ProductClassID Order By ID Desc";
                SqlParameter[] param = { DbHelper.MakeInParam("@ProductClassID", SqlDbType.Int, 4, ProductClassID) };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "Select * From " + Prefix + "Products  Where IsDelete=0   Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }

        }
        public static DataTable GetProductListWithKey(String keyWord)
        {
            String sql = "Select * From " + Prefix + "Products Where IsDelete=0 and  ProductName like '%" + keyWord + "%' Order By ID Desc";
            return DbHelper.ExecuteTable(sql);
        }
        public static DataTable GetProductListWithCondition(String condition)
        {
            String sql = "Select * From " + Prefix + "Products " + condition;
            if (condition.ToLower().Contains("order by"))
            {
                sql+= ",";
            }
            else
            {
                sql+= " Order By ";
            }
            sql+=" ID Desc ";
            return DbHelper.ExecuteTable(sql);
        }
        public static DataTable GetProductAll()
        {
            String sql = "Select * From " + Prefix + "Products where  IsDelete=0   Order By ID Desc";
            return DbHelper.ExecuteTable(sql);
        }

        public static DataTable GetNewsListWithChildColumn(Int32 NewsClassID)
        {
            String sql = String.Empty;
            if (NewsClassID != 0)
            {
                SqlParameter[] param={ 
                                           DbHelper.MakeInParam("@NewsClassID", SqlDbType.Int, 4, NewsClassID)
                                       };
                var addRoute = DbHelper.ExecuteTable("select b.AddressRoute from " + Prefix + "Column as b where b.id=@NewsClassID", param);
                if (Convert.ToString(addRoute.Rows[0]["AddressRoute"]) != "0")
                {
                    sql = "SELECT  *  FROM [" + Prefix + "NewsInfo] where NewsClassID in ( select c.id from " + Prefix + "Column as c where c.AddressRoute like '" + addRoute.Rows[0]["AddressRoute"] + "%')";
                    return DbHelper.ExecuteTable(sql);
                }
                else
                {
                    sql = "SELECT  *  FROM [" + Prefix + "NewsInfo] where NewsClassID  =" + NewsClassID + "";
                    return DbHelper.ExecuteTable(sql);
                }

            }
            else
            {
                sql = "Select  * From " + Prefix + "NewsInfo  Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }

        }
        public static DataTable GetProductListWithChildColumn(Int32 ProductClassID)
        {
            String sql = String.Empty;
            if (ProductClassID != 0)
            {
                //SqlParameter[] param={ 
                                       //    DbHelper.MakeInParam("@ProductClassID", SqlDbType.Int, 4, ProductClassID)
                                       //};
                //var addRoute = DbHelper.ExecuteTable("select top 1 b.AddressRoute from " + Prefix + "Column as b where b.id=@ProductClassID", param);
                //if (Convert.ToString(addRoute.Rows[0]["AddressRoute"]) != "0")
                //{
                //    StrSql = "SELECT  *  FROM [" + Prefix + "Products] where ProductClassID in ( select c.id from " + Prefix + "Column as c where c.AddressRoute like '" + addRoute.Rows[0]["AddressRoute"] + "%')";
                //    return DbHelper.ExecuteTable(StrSql);
                //}
                //else
                //{
                sql = "SELECT  *  FROM [" + Prefix + "Products] where  IsDelete=0 and ( ProductClassID=" + ProductClassID + " or  ProductClassID in ( select c.id from " + Prefix + "Column as c where c.AddressRoute like '0," + ProductClassID + "%'))";
                    return DbHelper.ExecuteTable(sql);
                //}

            }
            else
            {
                sql = "Select  * From " + Prefix + "Products where  IsDelete=0   Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }

        }
        public static DataTable GetProductListWithChildColumn(Int32 ProductClassID,Int32 showCol)
        {
            String sql = String.Empty;
            if (ProductClassID != 0)
            {
                sql = "SELECT    top " + showCol + " *  FROM [" + Prefix + "Products] where IsDelete=0 and  ( ProductClassID=" + ProductClassID + " or  ProductClassID  in ( select c.id from " + Prefix + "Column as c where IsDelete=0 and c.AddressRoute like '0," + ProductClassID + "%'))";
                    return DbHelper.ExecuteTable(sql);
            }
            else
            {
                sql = "Select  top " + showCol + " * From " + Prefix + "Products  where IsDelete=0  Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }

        }
        public static DataTable GetProductListWithChildColumn(Int32 ProductClassID, Int32 showCol,String Key)
        {
            String sql = String.Empty;
            if (ProductClassID != 0)
            {
                sql = "SELECT    top " + showCol + " *  FROM [" + Prefix + "Products] where IsDelete=0  and KeyWords like '%" + Key + "%' and  ( ProductClassID=" + ProductClassID + " or  ProductClassID  in ( select c.id from " + Prefix + "Column as c  where IsDelete=0 and  c.AddressRoute like '0," + ProductClassID + "%'))";
                return DbHelper.ExecuteTable(sql);
            }
            else
            {
                sql = "Select  top " + showCol + " * From " + Prefix + "Products  where IsDelete=0 and KeyWords like '%" + Key + "%'  Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }

        }
        public static DataTable GetProductList(Int32 ProductClassID,Int32 showCol)
        {
            String sql = String.Empty;
            if (ProductClassID != 0)
            {
                sql = "Select top " + showCol + " * From " + Prefix + "Products Where IsDelete=0 and ProductClassID=@ProductClassID Order By ID Desc";
                SqlParameter[] param = { 
                                           DbHelper.MakeInParam("@ProductClassID", SqlDbType.Int, 4, ProductClassID)
                                       };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "Select top " + showCol + " * From " + Prefix + "Products where  IsDelete=0  Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }

        }

        public static DataTable GetProductList(Int32 ProductClassID, Int32 showCol,String Key)
        {
            String sql = String.Empty;
            if (ProductClassID != 0)
            {
                sql = "Select top " + showCol + " * From " + Prefix + "Products Where IsDelete=0 and KeyWords like '%"+Key+"%' and ProductClassID=@ProductClassID Order By ID Desc";
                SqlParameter[] param = { 
                                           DbHelper.MakeInParam("@ProductClassID", SqlDbType.Int, 4, ProductClassID)
                                       };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "Select top " + showCol + " * From " + Prefix + "Products where  IsDelete=0 and KeyWords like '%" + Key + "%' Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }

        }
        #region "商品列表"
        /// <summary>
        /// 商品列表
        /// </summary>
        /// <param name="productClassID">栏目ID</param>
        /// <param name="showCol">显示行数</param>
        /// <param name="Key">搜索关键词</param>
        /// <param name="showRoot">实现类型</param>
        /// <returns>DataTable</returns>
        public static DataTable GetProductList(Int32 productClassID, Int32 showCol, String Key, String showRoot, String includeChild)
        {
            String condition = String.Empty;
            switch (showRoot)
            {
                case "0":
                    return GetAllProduct(showCol,Key);
                case "1":
                    return GetAllProduct(showCol, Key, productClassID, includeChild);
                case "2":
                    return GetAllParentProduct(showCol, Key, productClassID, includeChild);
                case "3":
                    return GetAllProduct(showCol, Key, productClassID, includeChild);
                case "4":
                    return GetAllParentProduct(showCol, Key, productClassID, includeChild);
                default:
                    return GetAllProduct(showCol, Key);
            }

        }
        #endregion 

        #region "读取指定行数的全部商品"
        /// <summary>
        /// 读取指定行数的全部商品
        /// </summary>
        /// <param name="showCol">行数</param>
        /// <param name="key">搜索的关键词</param>
        /// <returns>DataTable</returns>
        public static DataTable GetAllProduct(Int32 showCol,String key)
        {
            String sql = "Select top " + showCol + " * From [" + Prefix + "Products] where  [IsDelete]=0 ";
            if (!String.IsNullOrEmpty(key))
            {
                sql += " and [KeyWords] like '%" + key + "%'  ";
            }
            sql += " Order By [ID] Desc";
            return DbHelper.ExecuteTable(sql);
        }
        #endregion

        #region "读取指定栏目、指定行数的全部商品"
        /// <summary>
        /// 读取指定栏目、指定行数的全部商品
        /// </summary>
        /// <param name="showCol">行数</param>
        /// <param name="key">搜索的关键词</param>
        /// <param name="columnID">指定栏目</param>
        /// <param name="includeChild">是否包涵子栏目</param>
        /// <returns></returns>
        public static DataTable GetAllProduct(Int32 showCol, String key, Int32 columnID, String includeChild)
        {
            String sql = "Select top " + showCol + " * From [" + Prefix + "Products] where  [IsDelete]=0 and ( [ProductClassID]=@ProductClassID ";

            if (!String.IsNullOrEmpty(includeChild) && includeChild.ToLower() == "true")
            {
                String path = GetPath(Convert.ToInt32(columnID));
                if (!String.IsNullOrEmpty(path))
                {
                    String set = GetColumnSet(path + "," + columnID);
                    if (!String.IsNullOrEmpty(set))
                    {
                        sql += " or [ProductClassID] in (" + set + ") ";
                    }
                }
            }
            sql += " ) ";

            if (!String.IsNullOrEmpty(key))
            {
                sql += " and [KeyWords] like '%" + key + "%'  ";
            }
            sql += " Order By [ID] Desc";
            SqlParameter[] param = { 
                                           DbHelper.MakeInParam("@ProductClassID", SqlDbType.Int, 4, columnID)
                                       };
            return DbHelper.ExecuteTable(sql, param);
        }
        #endregion

        #region "读取指定栏目父级栏目、指定行数的全部商品"
        /// <summary>
        /// 读取指定栏目父级栏目、指定行数的全部商品
        /// </summary>
        /// <param name="showCol">行数</param>
        /// <param name="key">搜索的关键词</param>
        /// <param name="columnID">指定栏目父级栏目</param>
        /// <returns></returns>
        public static DataTable GetAllParentProduct(Int32 showCol, String key, Int32 columnID, String includeChild)
        {
            String sql = "Select top " + showCol + " * From [" + Prefix + "Products] where  [IsDelete]=0 ";
            SqlParameter[] param = null;
            if (columnID != 0)
            {
                Int32 parentID = GetParentID(columnID);
                if (parentID != 0)
                {
                    sql += " and ([ProductClassID]=@ProductClassID ";

                    if (!String.IsNullOrEmpty(includeChild) && includeChild.ToLower() == "true")
                    {
                        String path = GetPath(Convert.ToInt32(columnID));
                        if (!String.IsNullOrEmpty(path))
                        {
                            String set = GetColumnSet(path + "," + columnID);
                            if (!String.IsNullOrEmpty(set))
                            {
                                sql += " or [ProductClassID] in (" + set + ") ";
                            }
                        }
                    }

                    param =new SqlParameter[]{ DbHelper.MakeInParam("@ProductClassID", SqlDbType.Int, 4, columnID)};
                    sql += " ) ";
                }
            }

            if (!String.IsNullOrEmpty(key))
            {
                sql += " and [KeyWords] like '%" + key + "%'  ";
            }
            sql += " Order By [ID] Desc";

            return DbHelper.ExecuteTable(sql, param);
        }
        #endregion

        #region "根据商品ID集合读取商品"
        /// <summary>
        /// 根据商品ID集合读取商品
        /// </summary>
        /// <param name="idSet">商品集合</param>
        /// <param name="showCol">读取条数</param>
        /// <returns></returns>
        public static DataTable GetProductListByIDSet(String idSet, Int32 showCol)
        {
            String sql = String.Empty;

            if (String.IsNullOrEmpty(idSet))
            {
                return null;
            }
            else
            {
                sql = "Select top " + showCol + " * From [" + Prefix + "Products]  where [IsDelete]=0 and  [id] in (" + idSet + ") order by charindex(CAST([ID] as varchar(50)),'," + idSet + ",') asc";
                return DbHelper.ExecuteTable(sql);
            }
        }
        #endregion

        public static DataTable GetProductListByPruductID(Int32 productID, Int32 showCol)
        {
            String sql = String.Empty;
            DataTable dts = new DataTable();
            if (productID != 0)
            {
                sql = "select top 1 HotLink from " + Prefix + "Products where id=@ProductID";
                SqlParameter[] param = { 
                                           DbHelper.MakeInParam("@ProductID", SqlDbType.Int, 4, productID)
                                       };
                dts = DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "select top 1 HotLink from " + Prefix + "Products order by id desc";
                dts= DbHelper.ExecuteTable(sql);
            }
            var hotValues= String.Empty;
            if (dts!=null && dts.Rows.Count>0 && !String.IsNullOrEmpty(dts.Rows[0]["HotLink"].ToString()))
            {
                hotValues = dts.Rows[0]["HotLink"].ToString();
            }

            if (String.IsNullOrEmpty(hotValues))
            {
                return null;
            }
            else
            {
                sql = "Select top " + showCol + " * From " + Prefix + "Products  where IsDelete=0 and  id in (" + hotValues + ") Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }
        }
        public static DataTable GetProductListByNewsID(Int32 newsID, Int32 showCol)
        {
            String sql = String.Empty;
            DataTable dts = new DataTable();
            if (newsID != 0)
            {
                sql = "select top 1 HotLink from " + Prefix + "NewsInfo where id=@NewsID";
                SqlParameter[] param = { 
                                           DbHelper.MakeInParam("@NewsID", SqlDbType.Int, 4, newsID)
                                       };
                dts = DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "select top 1 HotLink from " + Prefix + "NewsInfo order by id desc";
                dts = DbHelper.ExecuteTable(sql);
            }
            var hotValues = String.Empty;
            if (dts != null && dts.Rows.Count > 0 && !String.IsNullOrEmpty(dts.Rows[0]["HotLink"].ToString()))
            {
                hotValues = dts.Rows[0]["HotLink"].ToString();
            }

            if (String.IsNullOrEmpty(hotValues))
            {
                return null;
            }
            else
            {
                sql = "Select top " + showCol + " * From " + Prefix + "Products Where IsDelete=0 and id in (" + hotValues + ") Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }
        }
        public static DataTable GetProductPic(Int32 productID, Int32 showCol)
        {
            String sql = "Select top " + showCol + " * From " + Prefix + "ProductsPic Where ProductID=@ProductID Order By ID Desc";
            SqlParameter[] param = { 
                                        DbHelper.MakeInParam("@ProductID", SqlDbType.Int, 4, productID)
                                    };
            return DbHelper.ExecuteTable(sql, param);
        }
        public static DataTable GetStandardValue(Int32 productID)
        {
            String sql = "select c.StandardValueIDs from " + Prefix + "ProductsDetail  as c  where c.IsShow=1 and c.ProductID=@ProductID Order By ID Desc";
            SqlParameter[] param = { 
                                        DbHelper.MakeInParam("@ProductID", SqlDbType.Int, 4, productID)
                                    };
            return DbHelper.ExecuteTable(sql, param);
        }
        public static DataTable GetProductStandard(String standardValues)
        {
            String sql = "select a.ID,a.StandardValueName,a.StandardValuePic,b.StandardName,b.StandardType from " + Prefix + "StandardValue as a left join " + Prefix + "Standard as b on a.StandardID= b.ID where a.ID in (" + standardValues + ") order by a.ID desc,b.StandardName asc,a.OrderID";
            return DbHelper.ExecuteTable(sql);
        }
        #endregion
        #region "友情链接列表"
        /// <summary>
        /// 友情链接列表
        /// </summary>
        /// <param name="ColumnID"></param>
        /// <returns></returns>
        public static DataTable GetFriendLinkList(Int32 classID)
        {
            String sql = String.Empty;
            if (classID != 0)
            {
                sql = "Select * From " + Prefix + "Links Where LinkClassID=@ClassID Order By OrderID ASC,ID ASC";
                SqlParameter[] param = { DbHelper.MakeInParam("@ClassID", SqlDbType.Int, 4, classID) };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "Select * From " + Prefix + "Links  Order By OrderID ASC,ID ASC";
                return DbHelper.ExecuteTable(sql);
            }

        }
        public static DataTable GetFriendLinkList(Int32 classID, Int32 showCol)
        {
            String sql = String.Empty;
            if (classID != 0)
            {
                sql = "Select top " + showCol + " * From " + Prefix + "Links Where LinkClassID=@ClassID Order By OrderID ASC,ID ASC";
                SqlParameter[] param = { DbHelper.MakeInParam("@ClassID", SqlDbType.Int, 4, classID) };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "Select top " + showCol + "  * From " + Prefix + "Links  Order By OrderID ASC,ID ASC";
                return DbHelper.ExecuteTable(sql);
            }

        }
        #endregion

        #region "文章分页"
        /// <summary>
        /// 带子栏目信息文章分页
        /// </summary>
        /// <param name="PageIndex">当前页</param>
        /// <param name="PageSize">每页显示</param>
        /// <param name="RecordCount">记录总数</param>
        /// <param name="PageCount">页码总数</param>
        /// <param name="SqlCondition">参数</param>
        /// <returns>分页后的数据表</returns>
        public static DataTable GetNewsListWithChildColumn(Int32 PageIndex, Int32 PageSize, out Int32 RecordCount, out Int32 PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                String AllFields = "*";
                String Condition = "[" + Prefix + "NewsInfo]  where  [IsDelete]=0 ";

                String classID = Convert.ToString(SqlCondition[0].value);//价格上限    
                SqlParameter[] param =null;
                if (!String.IsNullOrEmpty(classID) && Public.IsNumber(classID))
                {
                    Condition += " and ([NewsClassID]=@NewsClassID ";
                    String path=GetPath(Convert.ToInt32(classID));
                    if(!String.IsNullOrEmpty(path))
                    {
                        String set=GetColumnSet(path+","+classID);
                        if(!String.IsNullOrEmpty(set))
                        {
                            Condition += " or [NewsClassID] in ("+set+") ";
                        }
                    }
                    Condition += " ) ";
                    param = new SqlParameter[] { DbHelper.MakeInParam("@NewsClassID", SqlDbType.Int, 4, classID) };
                }
                String IndexField = "[ID]";
                String OrderFields = " order by [ID] desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount,param);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }

        /// <summary>
        /// 不带子栏目信息文章分页
        /// </summary>
        /// <param name="PageIndex">当前页</param>
        /// <param name="PageSize">每页显示</param>
        /// <param name="RecordCount">记录总数</param>
        /// <param name="PageCount">页码总数</param>
        /// <param name="SqlCondition">参数</param>
        /// <returns>分页后的数据表</returns>
        public static DataTable GetNewsList(Int32 PageIndex, Int32 PageSize, out Int32 RecordCount, out Int32 PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                String AllFields = "*";
                String Condition = "[" + Prefix + "NewsInfo]  where  [IsDelete]=0 ";

                String classID = Convert.ToString(SqlCondition[0].value);//价格上限    
                SqlParameter[] param =null;
                if (!String.IsNullOrEmpty(classID) && Public.IsNumber(classID))
                {
                    Condition += " and [NewsClassID]=@NewsClassID ";
                    param = new SqlParameter[] { DbHelper.MakeInParam("@NewsClassID", SqlDbType.Int, 4, classID) };
                }
                String IndexField = "[ID]";
                String OrderFields = " order by [ID] desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount,param);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }
        #endregion

        #region "文章列表"
        /// <summary>
        /// 文章列表
        /// </summary>
        /// <param name="ColumnID"></param>
        /// <returns></returns>
        public static DataTable GetNewsList(Int32 NewsClassID)
        {
            String sql = String.Empty;
            if (NewsClassID != 0)
            {
                sql = "Select * From " + Prefix + "NewsInfo Where NewsClassID=@NewsClassID Order By ID Desc";
                SqlParameter[] param = { DbHelper.MakeInParam("@NewsClassID", SqlDbType.Int, 4, NewsClassID) };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "Select * From " + Prefix + "NewsInfo  Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }

        }
        #endregion
        #region "读取指定条数的文章"
        /// <summary>
        /// 读取指定条数的文章
        /// </summary>
        /// <param name="NewsClassID"></param>
        /// <param name="showCol"></param>
        /// <returns></returns>
        public static DataTable GetNewsList(Int32 NewsClassID,Int32 showCol)
        {
            String sql = String.Empty;
            if (NewsClassID != 0)
            {
                sql = "Select top "+showCol+" * From " + Prefix + "NewsInfo Where NewsClassID=@NewsClassID Order By ID Desc";
                SqlParameter[] param = { 
                                           DbHelper.MakeInParam("@NewsClassID", SqlDbType.Int, 4, NewsClassID) 
                                       };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "Select top " + showCol + "  * From " + Prefix + "NewsInfo  Order By ID Desc";
                return DbHelper.ExecuteTable(sql);
            }

        }
        #endregion
        #region "文章正文"
        /// <summary>
        /// 文章信息
        /// </summary>
        /// <param name="ColumnID"></param>
        /// <param name="NewsID"></param>
        /// <returns></returns>
        public static DataTable GetNewsInfo(Int32 ColumnID, Int32 NewsID)
        {
            if (NewsID == 0)
            {
                if (ColumnID == 0)
                {
                    NewsID = GetNewsID();
                }
                else
                {
                    NewsID = GetNewsID(ColumnID);
                }
            }
            String sql = "Select * From " + Prefix + "NewsInfo Where ID=@ID";
            SqlParameter[] param = { DbHelper.MakeInParam("@ID", SqlDbType.Int, 4, NewsID) };
            return DbHelper.ExecuteTable(sql, param);
        }
        /// <summary>
        /// 文章内容
        /// </summary>
        /// <param name="NewsID"></param>
        /// <returns></returns>
        public static String GetNewsContent(Int32 NewsID)
        {
            String Str = String.Empty;
            String sql = "Select * From " + Prefix + "NewsInfo Where ID=@ID";
            SqlParameter[] param = { DbHelper.MakeInParam("@ID", SqlDbType.Int, 4, NewsID) };
            DataTable Dt=DbHelper.ExecuteTable(sql, param);
            if (Dt != null && Dt.Rows.Count > 0)
            {
                Str = Dt.Rows[0]["NewsContent"].ToString();
            }
            return Str;
        }
        /// <summary>
        /// 获取第一条文章序号
        /// </summary>
        /// <returns></returns>
        private static Int32 GetNewsID()
        {
            Int32 _NewsID = -1;
            String sql = "Select Top 1 ID From " + Prefix + "NewsInfo Order By ID Desc";
            DataTable Dt = DbHelper.ExecuteTable(sql,null);
            if (Dt != null && Dt.Rows.Count > 0)
            {
                _NewsID = Convert.ToInt32(Dt.Rows[0]["ID"]);
            }
            Dt.Dispose();
            Dt.Clear();
            return _NewsID;
        }
        /// <summary>
        /// 获取第一条文章序号
        /// </summary>
        /// <param name="ColumnID"></param>
        /// <returns></returns>
        private static Int32 GetNewsID(Int32 NewsClassID)
        {
            Int32 _NewsID = -1;
            String sql = "Select Top 1 ID From " + Prefix + "NewsInfo Where NewsClassID=@NewsClassID";
            SqlParameter[] param = { DbHelper.MakeInParam("@NewsClassID", SqlDbType.Int, 4, NewsClassID) };
            DataTable Dt = DbHelper.ExecuteTable(sql, param);
            if (Dt != null && Dt.Rows.Count > 0)
            {
                _NewsID = Convert.ToInt32(Dt.Rows[0]["ID"]);
            }
            Dt.Dispose();
            Dt.Clear();
            return _NewsID;
        }
        #endregion

        #region "品牌列表"
        public static DataTable GetBrankList(Int32 showCol)
        {
            String sql = "Select top " + showCol + " * From [" + Prefix + "Branks] Where [IsDetele]=0 Order By [OrderID] asc,[ID] Desc";
            return DbHelper.ExecuteTable(sql);
        }
        #endregion

        #region "广告列表"
        /// <summary>
        /// 广告列表
        /// </summary>
        /// <param name="ColumnID"></param>
        /// <returns></returns>
        public static DataTable GetAdsList(Int32 classID)
        {
            String sql = String.Empty;
            if (classID != 0)
            {
                sql = "Select * From " + Prefix + "Ads Where AdClassID=@ClassID Order By OrderID ASC,ID ASC";
                SqlParameter[] param = { DbHelper.MakeInParam("@ClassID", SqlDbType.Int, 4, classID) };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "Select * From " + Prefix + "Ads  Order By OrderID ASC,ID ASC";
                return DbHelper.ExecuteTable(sql);
            }

        }
        public static DataTable GetAdsList(Int32 classID, Int32 showCol)
        {
            String sql = String.Empty;
            if (classID != 0)
            {
                sql = "Select top " + showCol + " * From " + Prefix + "Ads Where AdClassID=@ClassID Order By OrderID ASC,ID ASC";
                SqlParameter[] param = { DbHelper.MakeInParam("@ClassID", SqlDbType.Int, 4, classID) };
                return DbHelper.ExecuteTable(sql, param);
            }
            else
            {
                sql = "Select top " + showCol + "  * From " + Prefix + "Ads  Order By OrderID ASC,ID ASC";
                return DbHelper.ExecuteTable(sql);
            }

        }
        #endregion


        #region "搜索结果的商品分页"
        /// <summary>
        /// 分页的商品
        /// </summary>
        /// <param name="PageIndex">当前页</param>
        /// <param name="PageSize">每页显示</param>
        /// <param name="RecordCount">记录总数</param>
        /// <param name="PageCount">页码总数</param>
        /// <param name="SqlCondition">参数</param>
        /// <returns>分页后的数据表</returns>
        public static DataTable GetProductListWithCondition(Int32 PageIndex, Int32 PageSize, out Int32 RecordCount, out Int32 PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                String AllFields = "*";
                String condition = "[" + Prefix + "Products]  where  [IsDelete]=0 ";

                String max = Convert.ToString(SqlCondition[0].value);//价格上限
                String min = Convert.ToString(SqlCondition[1].value);//价格下限
                String cid = Convert.ToString(SqlCondition[2].value);//栏目ID
                String bid = Convert.ToString(SqlCondition[3].value);//品牌ID
                String tid = Convert.ToString(SqlCondition[4].value);//产品类别
                String keyWord = Convert.ToString(SqlCondition[5].value);//关键词
                String standardValue = Convert.ToString(SqlCondition[6].value);//规格值ID
                String orderBy = Convert.ToString(SqlCondition[7].value);//排序字段
                String orderRole = Convert.ToString(SqlCondition[8].value);//排序规则，升序或者降序
                String tag = Convert.ToString(SqlCondition[9].value);//商品标签
                String kw = Convert.ToString(SqlCondition[10].value);//商品关键词
                SqlParameter[] param = null;
                SqlParameter[] param2 = new SqlParameter[]{};
                if (!String.IsNullOrEmpty(cid) && Public.IsNumber(cid))
                {
                    condition += " and ([ProductClassID]=@ProductClassID ";
                    String path = GetPath(Convert.ToInt32(cid));
                    if (!String.IsNullOrEmpty(path))
                    {
                        String set = GetColumnSet(path + "," + cid);
                        if (!String.IsNullOrEmpty(set))
                        {
                            condition += " or [ProductClassID] in (" + set + ") ";
                        }

                    }
                    condition += " ) ";
                    
                    param = new SqlParameter[] { DbHelper.MakeInParam("@ProductClassID", SqlDbType.Int, 4, cid) };
                }

                if (!String.IsNullOrEmpty(tid) && Public.IsNumber(tid))
                {
                    condition += " and [ProductTypeID]=" + tid;
                }
                if (!String.IsNullOrEmpty(bid) && Public.IsNumber(bid))
                {
                    condition += " and [BrandID]=" + bid;
                }
                if (!String.IsNullOrEmpty(min) && Public.IsNumber(min))
                {
                    condition += " and [TradePrice]>=" + min;
                }
                if (!String.IsNullOrEmpty(max) && Public.IsNumber(max))
                {
                    condition += " and [TradePrice]<=" + max;
                }
                if (!String.IsNullOrEmpty(keyWord))
                {
                    condition += " and [ProductName] like '%" + keyWord + "%'";
                }
                if (!String.IsNullOrEmpty(kw))
                {
                    condition += " and [KeyWords] like '%" + Public.FilterSql(kw) + "%'";
                }
                if (!String.IsNullOrEmpty(tag))
                {
                    condition += " and [ProductTags] like '%" + Public.FilterSql(tag) + "%'";
                }
                if (!String.IsNullOrEmpty(standardValue) && Public.IsNumericSet(standardValue))
                {
                    String svc = GetProductIDSet(standardValue);
                    if (!String.IsNullOrEmpty(svc))
                    {
                        condition += " and [ID] in ( " + svc + ")";
                    }
                }

                String IndexField = "[ID]";
                String OrderFields = " order by ";
                if (!String.IsNullOrEmpty(orderBy))
                {
                    OrderFields += " [" + orderBy + "] ";
                }
                else
                {
                    OrderFields += "[ID]";
                }
                if (!String.IsNullOrEmpty(orderRole) && (orderRole == "asc" || orderRole == "desc"))
                {
                    OrderFields += orderRole;
                }
                else
                {
                    OrderFields += " desc";
                }
                return DbHelper.ExecutePage(AllFields, condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, param);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }
        #endregion

        #region "商品分页"
        /// <summary>
        /// 带子栏目信息商品分页
        /// </summary>
        /// <param name="PageIndex">当前页</param>
        /// <param name="PageSize">每页显示</param>
        /// <param name="RecordCount">记录总数</param>
        /// <param name="PageCount">页码总数</param>
        /// <param name="SqlCondition">参数</param>
        /// <returns>分页后的数据表</returns>
        public static DataTable GetProductListWithChildColumn(Int32 PageIndex, Int32 PageSize, out Int32 RecordCount, out Int32 PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                String AllFields = "*";
                String Condition = "[" + Prefix + "Products]  where  [IsDelete]=0 ";

                String classID = Convert.ToString(SqlCondition[0].value);
                String productKey = Convert.ToString(SqlCondition[1].value);
                SqlParameter[] param = null;
                if (!String.IsNullOrEmpty(classID) && Public.IsNumber(classID))
                {
                    Condition += " and ([ProductClassID]=@ProductClassID ";
                    String path = GetPath(Convert.ToInt32(classID));
                    if (!String.IsNullOrEmpty(path))
                    {
                        String set = GetColumnSet(path + "," + classID);
                        if (!String.IsNullOrEmpty(set))
                        {
                            Condition += " or [ProductClassID] in (" + set + ") ";
                        }
                    }
                    Condition += " ) ";

                    param = new SqlParameter[] { DbHelper.MakeInParam("@ProductClassID", SqlDbType.Int, 4, classID) };
                }
                if (!String.IsNullOrEmpty(productKey))
                {
                    Condition += " and [KeyWords] like '%" + productKey + "%' ";
                }
                String IndexField = "[ID]";
                String OrderFields = " order by [ID] desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, param);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }

        /// <summary>
        /// 不带子栏目信息商品分页
        /// </summary>
        /// <param name="PageIndex">当前页</param>
        /// <param name="PageSize">每页显示</param>
        /// <param name="RecordCount">记录总数</param>
        /// <param name="PageCount">页码总数</param>
        /// <param name="SqlCondition">参数</param>
        /// <returns>分页后的数据表</returns>
        public static DataTable GetProductList(Int32 PageIndex, Int32 PageSize, out Int32 RecordCount, out Int32 PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                String AllFields = "*";
                String Condition = "[" + Prefix + "Products]  where  [IsDelete]=0 ";

                String classID = Convert.ToString(SqlCondition[0].value);
                String productKey = Convert.ToString(SqlCondition[1].value);
                SqlParameter[] param = null;
                if (!String.IsNullOrEmpty(classID) && Public.IsNumber(classID))
                {
                    Condition += " and [ProductClassID]=@ProductClassID ";
                    param = new SqlParameter[] { DbHelper.MakeInParam("@ProductClassID", SqlDbType.Int, 4, classID) };
                }
                if (!String.IsNullOrEmpty(productKey))
                {
                    Condition += " and [KeyWords] like '%" + productKey + "%' ";
                }
                String IndexField = "[ID]";
                String OrderFields = " order by [ID] desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, param);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }
        #endregion

        #region "栏目底层操作"
        /// <summary>
        /// 读取栏目路径
        /// </summary>
        /// <param name="classID">类ID</param>
        /// <returns>路径字符串</returns>
        public static String  GetPath(Int32 classID)
        {
            String path = String.Empty;
            String sql = "select top 1 [AddressRoute] from [" + Prefix + "Column] where [ID]=@ClassID";
            SqlParameter[] param = { 
                                           DbHelper.MakeInParam("@ClassID", SqlDbType.Int, 4, classID) 
                                       };
            DataTable dt = DbHelper.ExecuteTable(sql,param);
            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToString(dt.Rows[0]["AddressRoute"]);
            }
            else
            {
                return "0";
            }
        }
        /// <summary>
        /// 读取栏目ID集合
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static String GetColumnSet(String path)
        {
            String sql = "select [ID] from [" + Prefix + "Column] where [AddressRoute] like '" + path + "%'";
            DataTable dt = DbHelper.ExecuteTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                String set = String.Empty;
                foreach (DataRow dr in dt.Rows)
                {
                    set += Convert.ToString(dr["ID"]) + ",";
                }
                set = set.Remove(set.LastIndexOf(','));
                return set;
            }
            else
            {
                return String.Empty;
            }
        }
        #endregion

        #region "读取产品ID集合"
        /// <summary>
        /// 读取产品ID集合
        /// </summary>
        /// <param name="standardValue">规格ID</param>
        /// <returns>产品ID集合</returns>
        public static String GetProductIDSet(String standardValue)
        {
            String sql = "select distinct [ProductID] from [" + Prefix + "ProductsDetail] where [StandardValueIDs]=@StandardValueIDs ";
            SqlParameter[] param = { 
                                           DbHelper.MakeInParam("@StandardValueIDs", SqlDbType.VarChar, 100, standardValue) 
                                       };
            DataTable dt = DbHelper.ExecuteTable(sql,param);
            if (dt != null && dt.Rows.Count > 0)
            {
                String set = String.Empty;
                foreach (DataRow dr in dt.Rows)
                {
                    set += Convert.ToString(dr["ProductID"]) + ",";
                }
                set = set.Remove(set.LastIndexOf(','));
                return set;
            }
            else
            {
                return String.Empty;
            }
        }
        #endregion
    }
}
